"""
Case Type   : 触发器
Case Name   : 非触发器所有者不能DROP TRIGGER
Create At   : 2020/8/2
Owner       : @li-xin12345
Description :
    1.创建用户
    2.创建表
    3.创建触发器函数
    4.创建触发器
    5.删除触发器
    6.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.失败
    6.成功
History     :
"""

import os
import unittest
from yat.test import macro

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Trigger(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.u_name = 'u_trigger_0028'
        self.t_name = 't_trigger_0028_01'
        self.t_name2 = 't_trigger_0028_02'
        self.f_name = 'f_trigger_0028()'
        self.tri_name = 'tri_trigger_0028'

    def test_trigger(self):
        text = '-----step1:创建用户-----'
        self.log.info(text)
        sql = f"drop user if exists {self.u_name};" \
              f"create user {self.u_name} password '{macro.COMMON_PASSWD}';"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, res,
                      '执行失败' + text)

        text = '-----step2:建表-----'
        self.log.info(text)
        sql = f"drop table if exists {self.t_name};" \
              f"drop table if exists {self.t_name2};" \
              f"create table {self.t_name}(id1 int, id2 int, id3 int);" \
              f"create table {self.t_name2}(id1 int, id2 int, id3 int);"
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertTrue(res.count(self.constant.CREATE_TABLE_SUCCESS) == 2,
                      '执行失败' + text)

        text = '-----step3:创建触发器函数-----'
        self.log.info(text)
        sql = f'CREATE OR REPLACE FUNCTION {self.f_name} RETURNS ' \
              f'TRIGGER AS \$\$ DECLARE BEGIN ' \
              f'INSERT INTO {self.t_name2} VALUES(NEW.id1, NEW.id2, NEW.id3);' \
              f'RETURN NEW;' \
              f'END \$\$ LANGUAGE PLPGSQL;'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, res,
                      '执行失败' + text)

        text = '-----step4:创建触发器-----'
        self.log.info(text)
        sql = f'drop trigger if exists {self.tri_name} on {self.t_name};' \
              f'CREATE TRIGGER {self.tri_name} BEFORE INSERT ON ' \
              f'{self.t_name} FOR EACH ROW EXECUTE PROCEDURE ' \
              f'{self.f_name};'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.TRIGGER_CREATE_SUCCESS_MSG, res,
                      '执行失败' + text)

        text = '-----step5:删除触发器-----'
        self.log.info(text)
        sql = f'drop trigger {self.tri_name} ON {self.t_name} '
        excute_cmd = f'-U {self.u_name} -W "{macro.COMMON_PASSWD}" '
        res = self.pri_sh.execut_db_sql(sql, excute_cmd)
        self.log.info(res)
        err_msg = 'permission denied for relation'
        self.assertIn(err_msg, res, '执行失败' + text)

    def tearDown(self):
        self.log.info('------step6:清理环境-------')
        sql = f'DROP TRIGGER if exists {self.tri_name} ON {self.t_name};' \
              f'DROP FUNCTION if exists {self.f_name} cascade;' \
              f'DROP TABLE if exists {self.t_name};' \
              f'DROP TABLE if exists {self.t_name2};' \
              f'DROP USER if exists {self.u_name};'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertTrue(res.count(self.constant.TRIGGER_DROP_SUCCESS_MSG) == 1
                        and
                        res.count(self.constant.DROP_FUNCTION_SUCCESS_MSG) == 1
                        and res.count(self.constant.DROP_TABLE_SUCCESS) == 2
                        and res.count(self.constant.DROP_ROLE_SUCCESS_MSG) == 1)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')